Load from a database

Note: This loader is accessible only if your user account is a member of the Administrator workgroup, or the Named Workgroup for this tool. Administrator users can set the Named Workgroup using the Access Control button Access Control button for this loader (see Loader access control).

To import data from a database, click the Load from Database button Load from Database button in the Data Tools workspace. The Load from Database screen will be displayed. The following database types are supported:

  • Microsoft Access database file
  • Microsoft ODBC Data Source
  • Microsoft OLE DB Data Source
  • Microsoft SQL Server (including Azure SQL database service)
  • Microsoft SQL Server database file
  • Microsoft SQLite file
  • Oracle

Note: There is an important limitation for data loads from Excel files using the OLE DB data source type. A maximum of 256 file columns can be addressed for one data load. Note as well that blank columns within a column range will be counted against the 256 limit.

Security permissions for loader settings and templates

Security permissions based on workgroups and user accounts can be applied separately to batch import settings, and to mapping templates. Access to batch management controls, and to template management controls (save, edit, rename, delete, etc.), will depend on the 'effective permissions' which are granted to each user's account (i.e., the combination of user-level permissions and any workgroup permissions affecting that user).

Data Loader tool: Launch loader jobs from a Version hierarchy

The Data Loader tool runs in the Manage Inputs workspace in the Planning Space Web application and can be used to launch loader jobs based on existing defined 'batch presets' without a user needing access to the Load from Database tool itself.

There is a restriction that all of the loader mapping templates deployed in the batch preset are configured for 'Server Side Load', and hence only usable for 'Microsoft SQL Server' data sources.

See Data Loader from Database for details.

Database connection

The connection configuration for the database is stored in the mapping template. Connection is attempted automatically whenever a template is selected for use.

Import process

The import process consists of the following steps:

  1. On the Database Batch Import screen, select a mapping template and click the Connect button in the Advanced section. If the template contains a valid database connection it will be connected automatically and you will see a Refresh button instead. You need to select files to import, set up import batches and specify import settings (see Database Batch Import). For information about how to create mapping templates, see Create a database mapping template.
  2. On the Destination Dataflow Hierarchy Node screen, select the version of the hierarchy where you want to import your data; note that you can load data only if you have full access to the selected destination. Then select the destination node, observing the hierarchy rules that apply. For example, if fields are located under regions in the Dataflow hierarchy you will only be allowed to import fields by placing them under regions.
  3. The Pre-Import Confirmation screen gives you a summary of the import options. The Logging Options button opens a dialog where you can specify whether the import log file should contain detailed import information or not. To begin the import, click Next.
  4. Once the import has finished, a confirmation message and the View Log button will be displayed. Check the log file if the import had warnings or errors. Click Finish to close the wizard.